﻿package
{
	import flash.utils.ByteArray;
	import flash.data.*;
	import flash.events.SQLEvent;
	import flash.events.SQLErrorEvent;
	import flash.filesystem.File; 
	
	[Bindable]
	[Table(name='timers')]
	dynamic class TimerLog extends Object
	{
		public var timer_id:int;
		public var task_number:int;
		public var time:int;
		public var description:String;
		public var date:int;
		public var deleted:Boolean;

		private static var dbFile:File;
		private static var conn:SQLConnection;
		
		public function TimerLog()
		{
			if (conn == null) 
			{
				conn = new SQLConnection(); 
					 
				conn.addEventListener(SQLEvent.OPEN, openHandler); 
				conn.addEventListener(SQLEvent.RESULT, getResults); 
				conn.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
					 
				// The database file is in the application storage directory 
				dbFile = File.applicationStorageDirectory.resolvePath("timers.db"); 
				
				if(!dbFile.exists)
				{
					conn.open(dbFile, SQLMode.CREATE);
					conn.close();
				}
				conn.open(dbFile, SQLMode.UPDATE);

				var sqlStatement:SQLStatement = new SQLStatement();
				sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
				sqlStatement.sqlConnection = conn;
				sqlStatement.text = 'Create Table If Not Exists timers(timer_id INTEGER PRIMARY KEY, task_number INTEGER, time INTEGER, description TEXT, date TEXT, deleted BOOLEAN)';
				sqlStatement.execute();

				date = new Date();

				patch();
			}
		}

		public function patch()
		{
			try
			{
				execute_query("Insert Into timers(date) Values('a string');");
				execute_query("Delete From timers Where date='a string'");
			}
			catch(e)
			{
				execute_query("Alter Table timers Rename To timers_bk");
				execute_query('Create Table If Not Exists timers(timer_id INTEGER PRIMARY KEY, task_number INTEGER, time INTEGER, description TEXT, date TEXT, deleted BOOLEAN)');
				execute_query('Insert Into timers Select * From timers_bk');
				execute_query('Drop Table timers_bk');
				new Errors('Patch applied to timers table.');
			}
		}
		
		public function getResults(e:SQLEvent):void
		{
			//trace(e.target.data);
		}
		
		public function execute_query(sql)
		{
			trace(sql);
			Tasks(null);
			var sqlStatement:SQLStatement = new SQLStatement();
			sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			sqlStatement.sqlConnection = conn;
			sqlStatement.text = sql;
			sqlStatement.execute();
			return sqlStatement.getResult().data;
		}
		
		public function insert()
		{
			trace('Timer inserted...');
			TimerLog(null);
			var sqlStatement:SQLStatement = new SQLStatement();
			sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			sqlStatement.sqlConnection = conn;

			sqlStatement.text = 'Insert Into timers(task_number, description, date, deleted, time) Values(NULL, NULL, @DATE, 0, 0);';
			var now:Date = new Date();
			var timestamp = Date.parse(now.toLocaleDateString());
			sqlStatement.parameters['@DATE'] = timestamp;
			sqlStatement.execute();

			sqlStatement = new SQLStatement();
			sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			sqlStatement.sqlConnection = conn;
			sqlStatement.text = 'Select last_insert_rowid() As [timer_id]';
			sqlStatement.execute();

			var new_id = sqlStatement.getResult().data[0].timer_id;
			return new_id;
		}

		public function cleanup()
		{
			execute_query("Delete From Timers Where time<1 And task_number Is Null And description Is Null And deleted=1");
		}
		
		public function update()
		{
			trace('Timer updated...');
			TimerLog(null);
			var sqlStatement:SQLStatement = new SQLStatement();
			sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			sqlStatement.sqlConnection = conn;

			sqlStatement.text = 'Update timers Set task_number=@TASK_NUMBER, time=@TIME, description=@DESCRIPTION, deleted=@DELETED Where timer_id=@TIMERID';

			sqlStatement.parameters['@TIMERID'] = timer_id;
			sqlStatement.parameters['@TASK_NUMBER'] = task_number;
			sqlStatement.parameters['@TIME'] = time;
			sqlStatement.parameters['@DESCRIPTION'] = description;
			//sqlStatement.parameters['@DATE'] = new Date().toLocaleDateString();
			sqlStatement.parameters['@DELETED'] = deleted;

			sqlStatement.execute();
		}
		
		public function get_timer(where)
		{
			var timer = execute_query('Select * From timers '+where);
			try
			{
				set_details(timer[0]);
			}
			catch(e){}
			return timer;
		}

		public function loadTimers()
		{
			//deleteTimers();
			var timers = get_timers_from_current_date();

			for each(var timer in timers)
			{
				Main.new_timer(timer.timer_id, timer.time, timer.task_number, timer.description);
			}
		}

		public function get_all_dates()
		{
			return execute_query('Select Distinct date From timers Where date Like "1%" Order By date Desc');
		}

		public function get_timers_from_current_date(showDeleted=false)
		{
			var now:Date = new Date();
			var timestamp = Date.parse(now.toLocaleDateString())+'.0';
			if(!showDeleted)
			{
				deletedSQL = ' And deleted=0';
			}
			return get_timer("Where date='"+timestamp+"'"+deletedSQL);
		}

		function deleteTimers()
		{
			execute_query('Delete From timers Where time Is Null');
		}
			 
		function openHandler(event:SQLEvent):void 
		{ 
			//trace("the database was created successfully"); 
		} 
			 
		function errorHandler(event:SQLErrorEvent):void 
		{ 
			new Errors("Error message: "+event.error.message+"\nDetails: "+event.error.details); 
		} 
		
		public function set_details(timer:Object)
		{
			task_number = timer.task_number;
			time = timer.time;
			description = timer.description;
			date = timer.date;
			deleted = timer.deleted;
		}
	}
}